package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.FuncionarioExterno;

public class FuncionarioExternoDaoImpl implements FuncionarioExternoDao {

	Connection c;

	public FuncionarioExternoDaoImpl() {
		GenericDao gDao = new GenericDao();
		c = gDao.getConnection();
	}

	@Override
	public void salvaFuncionarioExterno(FuncionarioExterno funcionarioExterno)
			throws SQLException {
		String sql = "INSERT INTO funcionariosExternos VALUES (?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, funcionarioExterno.getIdFuncExt());
		ps.setString(2, funcionarioExterno.getNomeFuncExt());
		ps.setInt(3, funcionarioExterno.getCPF());
		ps.setString(4, funcionarioExterno.getEndereco());
		ps.setInt(5, funcionarioExterno.getTelefoneRes());
		ps.setInt(6, funcionarioExterno.getTelefoneCel());
		ps.setString(7, funcionarioExterno.getEmail());
		ps.setInt(8, funcionarioExterno.getCep());
		ps.setString(9, funcionarioExterno.getBairro());
		ps.setString(10, funcionarioExterno.getComplemento());
		ps.setInt(11, funcionarioExterno.getNum());

		ps.execute();
		ps.close();

	}

	@Override
	public void editaFuncionarioExterno(FuncionarioExterno funcionarioExterno)
			throws SQLException {
		String sql = "UPDATE funcionariosExternos SET nomeFuncExt = ?, "
				+ "endereco = ?, " + "telefoneRes = ?, " + "telefoneCel = ?, "
				+ "email = ?, " + "cep = ?, " + "bairro = ?, "
				+ "complemento = ?, " + "CPF = ?, "
				+ "num = ? WHERE idFuncExt = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, funcionarioExterno.getNomeFuncExt());
		ps.setInt(2, funcionarioExterno.getCPF());
		ps.setString(3, funcionarioExterno.getEndereco());
		ps.setInt(4, funcionarioExterno.getTelefoneRes());
		ps.setInt(5, funcionarioExterno.getTelefoneCel());
		ps.setString(6, funcionarioExterno.getEmail());
		ps.setInt(7, funcionarioExterno.getCep());
		ps.setString(8, funcionarioExterno.getBairro());
		ps.setString(9, funcionarioExterno.getComplemento());
		ps.setInt(10, funcionarioExterno.getNum());
		ps.setInt(11, funcionarioExterno.getIdFuncExt());

		ps.execute();
		ps.close();

	}

	@Override
	public void excluiFuncionarioExterno(FuncionarioExterno funcionarioExterno)
			throws SQLException {
		String sql = "DELETE funcionariosExternos WHERE idFuncExt = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, funcionarioExterno.getIdFuncExt());
		ps.execute();
		ps.close();

	}

	@Override
	public FuncionarioExterno pesquisaFuncionarioExterno(
			FuncionarioExterno funcionarioExterno) throws SQLException {
		FuncionarioExterno fe = new FuncionarioExterno();
		String sql = "SELECT CPF, nomeFuncExt, endereco, telefoneRes, telefoneCel, "
				+ "email, cep, bairro, complemento, num FROM funcionariosExternos "
				+ "WHERE idFuncExt = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, funcionarioExterno.getIdFuncExt());
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			fe.setIdFuncExt(rs.getInt("idFuncExt"));
			fe.setNomeFuncExt(rs.getString("nomeFuncExt"));
			fe.setEndereco(rs.getString("endereco"));
			fe.setTelefoneRes(rs.getInt("telefoneRes"));
			fe.setTelefoneCel(rs.getInt("telefoneCel"));
			fe.setEmail(rs.getString("email"));
			fe.setCep(rs.getInt("cep"));
			fe.setBairro(rs.getString("bairro"));
			fe.setComplemento(rs.getString("complemento"));
			fe.setNum(rs.getInt("num"));
			fe.setCPF(rs.getInt("CPF"));
		}
		ps.close();
		rs.close();
		return fe;
	}

}
